package com.demo2do.fms.admin.entity.report.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 *
 * @author Ark
 *
 */
public class ShipmentReportSearch implements QueryBuilder {

    /** 所选择公司 */
    private Long organizationId;
    
    /** 调度指令号 */
    private String shipmentNumber;
    
    /** 驳运车号 */
    private Long transMeansId;
    
    /** 托运客户 */
    private Long customerId;
    
    /** 制造商*/
    private Long manufactorId;
    
    /** 起始地 */
    private Long srcWhId;
    
    /** 目的地 装卸地 */
    private Long destWhId;
    
    /** 运输方式*/
    private String transType;
    
    /** 日期 */
    private String beginCreateDate;
    
    /** 日期 */
    private String endCreateDate;
    
    /** 开始装运日期 */
    private String beginLoadingDate;
    
    /** 结束装运日期 */
    private String endLoadingDate;
    
    /** 开始起运日期 */
    private String beginLeavingDate;
    
    /** 结束起运日期 */
    private String endLeavingDate;
    
    /** 调度指令状态 */
    private Long status;
    
    /** 查询安吉调度指令/生成派车单 */
    private String target;
    
    /** 导出条数上限 */
    private Integer limit;
    
    private QueryOrder order;
    
    private QueryFilter filter;
    
    /**
     * The default constructor
     */
    public ShipmentReportSearch() {
        order = new QueryOrder().add("s", "create_time", "DESC");
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (filter == null || refresh) {
            
            filter = new QueryFilter().addEquals("v", "organization_id", organizationId)
                                      .addEquals("s", "shipment_number", shipmentNumber)
                                      .addEquals("v", "id", transMeansId)
                                      .addEquals("c", "id", customerId)
                                      .addEquals("s", "manufactor_id", manufactorId)
                                      .addEquals("s", "src_wh_id", srcWhId)
                                      .addEquals("s", "dest_wh_id", destWhId)
                                      .addEquals("s", "trans_type", transType);
            
            if (StringUtils.isNotBlank(beginCreateDate)) {
                filter.addSentence("s.create_time >= TO_DATE('" + beginCreateDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endCreateDate)) {
                filter.addSentence("s.create_time <= TO_DATE('" + endCreateDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(beginLoadingDate)) {
                filter.addSentence("s.loading_time >= TO_DATE('" + beginLoadingDate.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endLoadingDate)) {
                filter.addSentence("s.loading_time <= TO_DATE('" + endLoadingDate.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(beginLeavingDate)) {
                filter.addSentence("s.leaving_time >= TO_DATE('" + beginLeavingDate.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endLeavingDate)) {
                filter.addSentence("s.leaving_time <= TO_DATE('" + endLeavingDate.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            filter.addEquals("s", "status", status);
            
            // 生成派车单查询时，需要加上派车单为空的条件
            if (StringUtils.equals("generate", target)) {
                filter.addIsNull("s", "dispatch_order_id");
            }
            
        }
        
        return filter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT v.organization_name AS organization, ")
                     .append("s.shipment_number AS shipmentNumber, ")
                     .append("s.shipment_group AS shipmentGroup, ")
                     .append("c.organization_code || ' - ' || c.organization_name AS client, ")
                     .append("s.manufactor_code || ' - ' || s.manufactor_name AS manufactor, ")
                     .append("s.create_time AS createTime, ")
                     .append("w1.facility_code || ' - ' || w1.facility_name AS startPlace, ")
                     .append("w2.facility_code || ' - ' || w2.facility_name AS endPlace, ")
                     .append("v.vehicle_code || ' - ' || v.vehicle_name AS vehicle, ")
                     .append("s.load_quantity AS loadQuantity, ")
                     .append("s.loading_time AS loadingTime, ")
                     .append("s.leaving_time AS leavingTime, ")
                     .append("s.drive_way AS driveWay, ")
                     .append("FC_GET_SHIPMENT_STATUS(s.status) AS shipmentStatus, ")
                     .append("FC_GET_SHIPMENT_METHOD(TRIM(s.trans_type)) AS transType, ")
                     .append("s.is_accident AS accident ")
               .append("FROM vw_shipment_line s, vw_warehouse w1, vw_warehouse w2, vw_vehicle v, vw_client c ")
               .append("WHERE s.src_wh_id = w1.id AND s.dest_wh_id = w2.id AND s.trans_means_id = v.id AND s.is_disabled = 0 AND s.customer_id = c.id(+) ")
               .append(getPreparedFilter(false).clauseSentence() + order.sentence());
        
        String sentence = buffer.toString();
        if (limit != null) {
            sentence = "SELECT * FROM (" + sentence + ") WHERE ROWNUM <=" + limit;
        }
        
        return sentence;
        
    }
    
    /**
     * build row mapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("organization", rs.getObject("organization"));
                result.put("shipmentNumber", rs.getObject("shipmentNumber"));
                result.put("shipmentGroup", rs.getObject("shipmentGroup"));
                result.put("createTime", rs.getObject("createTime"));
                result.put("client", rs.getObject("client"));
                result.put("manufactor", rs.getObject("manufactor"));
                result.put("startPlace", rs.getObject("startPlace"));
                result.put("endPlace", rs.getObject("endPlace"));
                result.put("vehicle", rs.getObject("vehicle"));
                result.put("loadQuantity", rs.getObject("loadQuantity"));
                result.put("loadingTime", rs.getObject("loadingTime"));
                result.put("leavingTime", rs.getObject("leavingTime"));
                result.put("driveWay", rs.getObject("driveWay"));
                result.put("shipmentStatus", rs.getObject("shipmentStatus"));
                result.put("transType", rs.getObject("transType"));
                result.put("accident", rs.getBoolean("accident"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return order;
    }
    
    /**
     * @return
     */
    public QueryFilter getFilter(){
        return getPreparedFilter(false);
    }
    
    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the shipmentNumber
     */
    public String getShipmentNumber() {
        return shipmentNumber;
    }

    /**
     * @return the transMeansId
     */
    public Long getTransMeansId() {
        return transMeansId;
    }

    /**
     * @return the customerId
     */
    public Long getCustomerId() {
        return customerId;
    }

    /**
     * @return the manufactorId
     */
    public Long getManufactorId() {
        return manufactorId;
    }

    /**
     * @return the srcWhId
     */
    public Long getSrcWhId() {
        return srcWhId;
    }

    /**
     * @return the destWhId
     */
    public Long getDestWhId() {
        return destWhId;
    }

    /**
     * @return
     */
    public String getTransType() {
        return transType;
    }

    /**
     * @return
     */
    public String getBeginCreateDate() {
        return beginCreateDate;
    }
    
    /**
     * @return
     */
    public String getEndCreateDate() {
        return endCreateDate;
    }

    /**
     * @return the beginLoadingDate
     */
    public String getBeginLoadingDate() {
        return beginLoadingDate;
    }

    /**
     * @return the endLoadingDate
     */
    public String getEndLoadingDate() {
        return endLoadingDate;
    }

    /**
     * @return the beginLeavingDate
     */
    public String getBeginLeavingDate() {
        return beginLeavingDate;
    }

    /**
     * @return the endLeavingDate
     */
    public String getEndLeavingDate() {
        return endLeavingDate;
    }

    /**
     * @return the status
     */
    public Long getStatus() {
        return status;
    }

    /**
     * @return the target
     */
    public String getTarget() {
        return target;
    }
    
    /**
     * @return
     */
    public Integer getLimit() {
        return limit;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param shipmentNumber the shipmentNumber to set
     */
    public void setShipmentNumber(String shipmentNumber) {
        this.shipmentNumber = shipmentNumber;
    }

    /**
     * @param transMeansId the transMeansId to set
     */
    public void setTransMeansId(Long transMeansId) {
        this.transMeansId = transMeansId;
    }

    /**
     * @param customerId the customerId to set
     */
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    /**
     * @param manufactorId the manufactorId to set
     */
    public void setManufactorId(Long manufactorId) {
        this.manufactorId = manufactorId;
    }

    /**
     * @param srcWhId the srcWhId to set
     */
    public void setSrcWhId(Long srcWhId) {
        this.srcWhId = srcWhId;
    }

    /**
     * @param destWhId the destWhId to set
     */
    public void setDestWhId(Long destWhId) {
        this.destWhId = destWhId;
    }

    /**
     * @param transType
     */
    public void setTransType(String transType) {
        this.transType = transType;
    }

    /**
     * @param beginCreateDate
     */
    public void setBeginCreateDate(String beginCreateDate) {
        this.beginCreateDate = beginCreateDate;
    }
    
    /**
     * @param endCreateDate
     */
    public void setEndCreateDate(String endCreateDate) {
        this.endCreateDate = endCreateDate;
    }

    /**
     * @param beginLoadingDate the beginLoadingDate to set
     */
    public void setBeginLoadingDate(String beginLoadingDate) {
        this.beginLoadingDate = beginLoadingDate;
    }

    /**
     * @param endLoadingDate the endLoadingDate to set
     */
    public void setEndLoadingDate(String endLoadingDate) {
        this.endLoadingDate = endLoadingDate;
    }

    /**
     * @param beginLeavingDate the beginLeavingDate to set
     */
    public void setBeginLeavingDate(String beginLeavingDate) {
        this.beginLeavingDate = beginLeavingDate;
    }

    /**
     * @param endLeavingDate the endLeavingDate to set
     */
    public void setEndLeavingDate(String endLeavingDate) {
        this.endLeavingDate = endLeavingDate;
    }

    /**
     * @param status the status to set
     */
    public void setStatus(Long status) {
        this.status = status;
    }

    /**
     * @param target the target to set
     */
    public void setTarget(String target) {
        this.target = target;
    }
    
    /**
     * @param limit
     */
    public void setLimit(Integer limit) {
        this.limit = limit;
    }
    
}
